/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package cronos.application;

import cronos.dao.DAO;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author Leonardo Oliveira Moreira
 */
public class ApplicationDAO extends DAO {

    public ApplicationDAO() throws SQLException {
    }

    public void insert(Application application) throws SQLException {
        PreparedStatement preparedStatement = connection.prepareStatement("INSERT INTO application (name) VALUES (?)");
        preparedStatement.setString(1, application.getName());
        int result = preparedStatement.executeUpdate();
        if (result == 1) {
            connection.commit();
        } else {
            connection.rollback();
        }
    }

    public void update(Application application) throws SQLException {
        PreparedStatement preparedStatement = connection.prepareStatement("UPDATE application SET name = ? WHERE id = ?");
        preparedStatement.setString(1, application.getName());
        preparedStatement.setLong(2, application.getId());
        int result = preparedStatement.executeUpdate();
        if (result == 1) {
            connection.commit();
        } else {
            connection.rollback();
        }
    }

    public void delete(Application application) throws SQLException {
        PreparedStatement preparedStatement = connection.prepareStatement("DELETE FROM application WHERE id = ?");
        preparedStatement.setLong(1, application.getId());
        int result = preparedStatement.executeUpdate();
        if (result == 1) {
            connection.commit();
        } else {
            connection.rollback();
        }
    }
    
    public List<Application> get(Integer id, String name) throws SQLException {
        if (id == null) {
            id = -1;
        }
        if (name == null) {
            name = "%";
        } else {
            name = "%" + name + "%";
        }
        List<Application> result = new ArrayList<Application>();
        try {
            PreparedStatement preparedStatement = connection.prepareStatement("SELECT id, name FROM application WHERE (id = ? OR -1 = ?) AND (UPPER(name) LIKE UPPER(?)) ORDER BY name ASC");
            preparedStatement.setInt(1, id);
            preparedStatement.setInt(2, id);
            preparedStatement.setString(3, name);
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet != null && resultSet.next()) {
                Application application = new Application();
                application.setId(resultSet.getInt("id"));
                application.setName(resultSet.getString("name"));
                result.add(application);
            }
            resultSet.close();
            preparedStatement.close();
            connection.commit();
        } catch (SQLException ex) {
            connection.rollback();
            throw ex;
        }
        return result;
    }

    public List<Application> getAll() throws SQLException {
        List<Application> result = new ArrayList<Application>();
        try {
            PreparedStatement preparedStatement = connection.prepareStatement("SELECT id, name FROM application ORDER BY name ASC");
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet != null && resultSet.next()) {
                Application application = new Application();
                application.setId(resultSet.getInt("id"));
                application.setName(resultSet.getString("name"));
                result.add(application);
            }
            resultSet.close();
            preparedStatement.close();
            connection.commit();
        } catch (SQLException ex) {
            connection.rollback();
            throw ex;
        }
        return result;
    }

    public Application getById(int id) throws SQLException {
        Application result = null;
        try {
            PreparedStatement preparedStatement = connection.prepareStatement("SELECT id, name FROM application WHERE id = ?");
            preparedStatement.setInt(1, id);
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet != null && resultSet.next()) {
                result = new Application();
                result.setId(resultSet.getInt("id"));
                result.setName(resultSet.getString("name"));
                break;
            }
            resultSet.close();
            preparedStatement.close();
            connection.commit();
        } catch (SQLException ex) {
            connection.rollback();
            throw ex;
        }
        return result;
    }
}